﻿using CNKI.TPI.Web.Admin.IDAO;
using CNKI.TPI.Web.Admin.Model;
using CNKI.TPI.Web.Base;
using CNKI.TPI.Web.Search.Model;
using KNet.Data.Entity;
using KNet.Data.KBaseClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace CNKI.TPI.Web.Admin.DAO
{
    public class MultiDBPublishDAO : BaseDAO, IMultiDBPublishDAO
    {
        /// <summary>
        /// 保存跨库分类
        /// </summary>
        /// <returns></returns>
        public bool SaveDBClass(DBCLASS obj)
        {
            bool result = false;
            try
            {
                result = Context.Insert<DBCLASS>(obj);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }
        /// <summary>
        /// 修改跨库分类
        /// </summary>
        /// <returns></returns>
        public bool UpdateDBClass(string name, string code)
        {
            bool result = false;
            try
            {
                string sql = " update DBCLASS set CLASSNAME=" + name + " where CLASSCODE=" + code;
                result = Context.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }
        /// <summary>
        /// 删除跨库分类
        /// </summary>
        /// <returns></returns>
        public bool DeleteDBClass(string code)
        {
            bool result = false;
            try
            {
                string sql = "delete FROM DBCLASS where classcode ='" + code + "'";
                if (code.IndexOf('+') >= 0)
                {
                    sql = "delete FROM DBCLASS where classcode =(" + code + ")";
                }
                result = Context.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }

        /// <summary>
        /// 更新DBconfig
        /// </summary>
        /// <param name="configList"></param>
        /// <returns></returns>
        public bool UpdateDBClassFromDBConfig(IList<DBCONFIG> configList)
        {
            bool result = false;
            try
            {
                foreach (DBCONFIG item in configList)
                {
                    result = Context.Update(item);
                }
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }

            return result;
        }

        /// <summary>
        /// 插入DBconfig
        /// </summary>
        /// <param name="configList"></param>
        /// <returns></returns>
        public bool AddDBConfig(IList<DBCONFIG> configList)
        {
            bool result = false;
            try
            {
                foreach (DBCONFIG item in configList)
                {
                    result = Context.Insert(item);
                }
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }

            return result;
        }

        /// <summary>
        /// 保存设置的字段，（排序，检索，概览）
        /// </summary>
        /// <returns></returns>
        public bool SaveUsField(DBUSFIELD obj)
        {
            bool result = false;
            try
            {
                result = Context.Insert<DBUSFIELD>(obj);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }

        /// <summary>
        /// 更新设置的字段，（排序，检索，概览）
        /// </summary>
        /// <returns></returns>
        public bool UpdateUsField(DBUSFIELD obj)
        {
            bool result = false;
            try
            {
                result = Context.Update<DBUSFIELD>(obj);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }

        /// <summary>
        /// 删除设置的字段，（排序，检索，概览）
        /// </summary>
        /// <returns></returns>
        public bool DeleteUsField(Expression<Func<DBUSFIELD, bool>> query)
        {
            bool result = false;
            try
            {
                result = Context.Delete<DBUSFIELD>(query);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }


        /// <summary>
        /// 获取所有已经发布的数据库列表
        /// </summary>
        /// <returns></returns>
        public IList<SYS_CMS_DATABASE> GetPublishDBList(PermissionOption option = PermissionOption.FilteringPermissions, bool visible = true)
        {

            IList<SYS_CMS_DATABASE> result = null;
            try
            {
                ExpressionCombine<SYS_CMS_DATABASE> exp = new ExpressionCombine<SYS_CMS_DATABASE>();
                exp.And("DatabasePubFlag", OperatorType.Equal, 1);
                if (visible)
                {
                    exp.And("VisibleFlag", OperatorType.Equal, 1);
                }

                if (SessionHelper.IsUserInfoExsit() && option == PermissionOption.FilteringPermissions)
                {
                    ExpressionCombine<SYS_CMS_DATABASE> subExp2 = new ExpressionCombine<SYS_CMS_DATABASE>();
                    IList<int> userDBInfo = SessionHelper.GetUserDBRight();
                    if (userDBInfo != null && userDBInfo.Count > 0)
                    {
                        foreach (int item in userDBInfo)
                        {
                            subExp2.Or("SysID", OperatorType.Equal, item);
                        }
                        exp.And(subExp2);
                    }

                    IList<string> securityLevel = SessionHelper.GetUserSecurity();
                    if (securityLevel != null && securityLevel.Count > 0)
                    {
                        ExpressionCombine<SYS_CMS_DATABASE> subExp3 = new ExpressionCombine<SYS_CMS_DATABASE>();
                        foreach (string security in securityLevel)
                        {
                            subExp3.Or("Secrity", OperatorType.Equal, security);
                        }
                        exp.And(subExp3);
                    }
                }
                Expression<Func<SYS_CMS_DATABASE, bool>> order = c => c.SortSn.Asc();
                result = Context.Get<SYS_CMS_DATABASE>(exp.Compile(), order);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }

            return result;
        }

        /// <summary>
        /// 根据数据库ID获得对应的映射关系
        /// </summary>
        /// <param name="dbID">数据库ID</param>
        /// <returns>映射关系列表</returns>
        public IList<SYS_CMS_US_FIELDMAP> GetUSFieldMap(int dbID)
        {
            IList<SYS_CMS_US_FIELDMAP> result = new List<SYS_CMS_US_FIELDMAP>();
            try
            {
                ExpressionCombine<SYS_CMS_US_FIELDMAP> exp = new ExpressionCombine<SYS_CMS_US_FIELDMAP>();
                exp.And("DBID", OperatorType.Equal, dbID);
                result = Context.Get<SYS_CMS_US_FIELDMAP>(exp.Compile());
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }

            return result;
        }

        /// <summary>
        /// 根据主键ID获得对应的映射关系
        /// </summary>
        /// <param name="dbID">主键ID</param>
        /// <returns>映射关系列表</returns>
        public SYS_CMS_US_FIELDMAP GetUSFieldMapByID(int sysID)
        {
            SYS_CMS_US_FIELDMAP result = null;
            try
            {
                ExpressionCombine<SYS_CMS_US_FIELDMAP> exp = new ExpressionCombine<SYS_CMS_US_FIELDMAP>();
                exp.And("SYSID", OperatorType.Equal, sysID);
                IList<SYS_CMS_US_FIELDMAP> content = Context.Get<SYS_CMS_US_FIELDMAP>(exp.Compile());
                if (content != null && content.Count > 0)
                {
                    result = content[0];
                }
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fieldIDList"></param>
        /// <returns></returns>
        public IList<SYS_CMS_US_FIELD> GetUSFieldByID(IList<int> fieldIDList)
        {
            IList<SYS_CMS_US_FIELD> result = new List<SYS_CMS_US_FIELD>();
            try
            {
                if (fieldIDList.Count() == 0)
                {
                    return new List<SYS_CMS_US_FIELD>();
                }
                ExpressionCombine<SYS_CMS_US_FIELD> exp = new ExpressionCombine<SYS_CMS_US_FIELD>();
                foreach (int item in fieldIDList)
                {
                    exp.Or("SYSID", OperatorType.Equal, item);
                }
                result = Context.Get<SYS_CMS_US_FIELD>(exp.Compile());
                //IList<SYS_CMS_US_FIELD> fieldList = context.GetAll<SYS_CMS_US_FIELD>();
                //if (fieldList != null && fieldList.Count > 0)
                //{
                //    result = fieldList;
                //}
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }

        /// <summary>
        /// 更新SYS_CMS_US_FIELDMAP
        /// </summary>
        /// <param name="configList"></param>
        /// <returns></returns>
        public bool UpdateFIELDMAP(SYS_CMS_US_FIELDMAP fieldmap)
        {
            bool result = false;
            try
            {
                result = Context.Update(fieldmap);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }

            return result;
        }

        /// <summary>
        /// 插入SYS_CMS_US_FIELDMAP
        /// </summary>
        /// <param name="configList"></param>
        /// <returns></returns>
        public bool AddFIELDMAP(SYS_CMS_US_FIELDMAP fieldmap)
        {
            bool result = false;
            try
            {
                result = Context.Insert(fieldmap);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }

            return result;
        }

        /// <summary>
        /// 删除SYS_CMS_US_FIELDMAP
        /// </summary>
        /// <param name="configList"></param>
        /// <returns></returns>
        public bool DeleteFIELDMAP(SYS_CMS_US_FIELDMAP fieldmap)
        {
            bool result = false;
            try
            {
                result = Context.Delete(fieldmap);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }

            return result;
        }
        /// <summary>
        /// 判断数据库中的字段是否已经设置过映射
        /// </summary>
        /// <param name="dbid"></param>
        /// <param name="dbfieldid"></param>
        /// <returns></returns>
        public bool IsExitstDbField(string dbid, string dbfieldid)
        {
            bool result = false;
            string sql = "SELECT * FROM SYS_CMS_US_FIELDMAP where dbid=" + dbid + " and fieldid=" + dbfieldid;
            IList<SYS_CMS_US_FIELDMAP> obj = null;
            try
            {
                obj = Context.ExecuteSql<SYS_CMS_US_FIELDMAP>(sql).ToList();
                if (obj != null && obj.Count > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }

            return result;
        }
        /// <summary>
        /// 判断公共字段是否已经设置过映射
        /// </summary>
        /// <param name="dbid"></param>
        /// <param name="USfieldid"></param>
        /// <returns></returns>
        public bool IsExitstUSField(string dbid, string usfieldid)
        {
            bool result = false;
            string sql = "SELECT * FROM SYS_CMS_US_FIELDMAP where dbid=" + dbid + " and usfieldid=" + usfieldid;
            IList<SYS_CMS_US_FIELDMAP> obj = null;
            try
            {
                obj = Context.ExecuteSql<SYS_CMS_US_FIELDMAP>(sql).ToList();
                if (obj != null && obj.Count > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }

            return result;
        }
        /// <summary>
        /// 修改时判断公共字段是否已经设置过映射
        /// </summary>
        /// <param name="dbid"></param>
        /// <param name="USfieldid"></param>
        /// <returns></returns>
        public bool IsExitstUSField(string dbid, string usfieldid, string sysid)
        {
            bool result = false;
            string sql = "SELECT * FROM SYS_CMS_US_FIELDMAP where dbid=" + dbid + " and usfieldid=" + usfieldid;
            IList<SYS_CMS_US_FIELDMAP> obj = null;
            try
            {
                obj = Context.ExecuteSql<SYS_CMS_US_FIELDMAP>(sql);
                if (obj != null && obj.Count > 0 && obj[0].SysID.ToString() != sysid)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }

            return result;
        }
        /// <summary>
        /// 获取数据字段
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public IList<SYS_CMS_DATABASE_FIELD> GetDBFiledByDbCode(string code)
        {
            string sql = "SELECT * FROM SYS_CMS_DATABASE_FIELD where databasecode=" + code;
            IList<SYS_CMS_DATABASE_FIELD> obj = null;
            try
            {
                obj = Context.ExecuteSql<SYS_CMS_DATABASE_FIELD>(sql).ToList();
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }

            return obj;
        }
        /// <summary>
        /// 获取公共映射字段
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public IList<SYS_CMS_US_FIELD> GetUSFiled()
        {
            IList<SYS_CMS_US_FIELD> result = new List<SYS_CMS_US_FIELD>();
            try
            {
                result = Context.GetAll<SYS_CMS_US_FIELD>();
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }
        /// <summary>
        /// 根据名称获取公共映射字段
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public IList<SYS_CMS_US_FIELD> GetUSFiledByName(string name)
        {
            IList<SYS_CMS_US_FIELD> result = null;
            try
            {
                ExpressionCombine<SYS_CMS_US_FIELD> exp = new ExpressionCombine<SYS_CMS_US_FIELD>();
                exp.And("Name", OperatorType.Equal, name);
                result = Context.Get<SYS_CMS_US_FIELD>(exp.Compile());
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }

        public HS_TABLE_FIELD GetFieldInfo(string dbCode, string fieldName)
        {
            HS_TABLE_FIELD result = new HS_TABLE_FIELD();
            try
            {
                result = Context.GetFieldInfo(dbCode + "_METADATA", fieldName);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }

        public int UpdateFieldInfo(string dbCode, HS_TABLE_FIELD fieldInfo)
        {
            int result = 0;
            try
            {
                string sql = "ALTER TABLE {0}_METADATA ALTER {1} AS {1} {2}({3}) {4} INDEX {5} {6}";


                StringBuilder fieldType = new StringBuilder(256);
                TPIClient.KBase_GetFieldTypeNameBySN(fieldInfo.Data, fieldType);
                //string fieldType = ((ColumnType)fieldInfo.Type).ToString().Substring(4);
                string index = ((FieldIndexType)fieldInfo.Index).ToString();
                string indexType = ((IndexMode)fieldInfo.IndexType).ToString().Substring(6);
                string encode = fieldInfo.DataCoding == 0 ? "ASCII" : "UNICODE";
                sql = string.Format(sql, dbCode, fieldInfo.Name, fieldType.ToString(), fieldInfo.Len, encode, index, indexType);
                if (!string.IsNullOrEmpty(fieldInfo.AliasName))
                {
                    sql += " ALIASNAME " + fieldInfo.AliasName;
                }
                if (!string.IsNullOrEmpty(fieldInfo.DispName))
                {
                    sql += " DISPLAYNAME " + fieldInfo.DispName;
                }
                result = Context.ExecuteSqlNonQuery(sql);
                sql = "update SYS_CMS_DATABASE_FIELD set alias='{0}' where databasecode='{1}' and field='{2}'";
                result = Context.ExecuteSqlNonQuery(string.Format(sql, fieldInfo.AliasName, dbCode, fieldInfo.Name));
                sql = "update {1}_FIELD set alias='{0}' where databasecode='{1}' and field='{2}'";
                result = Context.ExecuteSqlNonQuery(string.Format(sql, fieldInfo.AliasName, dbCode, fieldInfo.Name));
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }

        /// <summary>
        /// 保存数据库信息
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool SaveDB(SYS_CMS_DATABASE obj)
        {
            bool result = false;
            try
            {
                result = Context.Insert<SYS_CMS_DATABASE>(obj);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }
        /// <summary>
        /// 根据数据库信息
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool UpdateDB(SYS_CMS_DATABASE obj)
        {
            bool result = false;
            try
            {
                result = Context.Update<SYS_CMS_DATABASE>(obj);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }
        /// <summary>
        /// 保存数据库字段
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool SaveDBField(SYS_CMS_DATABASE_FIELD obj)
        {
            bool result = false;
            try
            {
                result = Context.Insert<SYS_CMS_DATABASE_FIELD>(obj);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }
        /// <summary>
        /// 删除对应的数据库字段
        /// </summary>
        /// <param name="dbcode"></param>
        /// <returns></returns>
        public bool DelDBField(string strid)
        {
            bool result = false;
            try
            {
                strid = strid.TrimEnd(',').TrimStart(',');
                string sql = "delete FROM SYS_CMS_DATABASE_FIELD where sysid ='" + strid + "'";
                if (strid.IndexOf(',') >= 0)
                {
                    strid = strid.Replace(',', '+');
                    sql = "delete FROM SYS_CMS_DATABASE_FIELD where sysid =(" + strid + ")";
                }
                result = Context.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }
        /// <summary>
        /// 删除数据库
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelDB(string id)
        {
            bool result = false;
            try
            {
                string sql = "";
                id = id.TrimEnd(',').TrimStart(',');
                if (id.IndexOf(',') >= 0)
                {
                    id = id.Replace(',', '+');
                    sql = "delete FROM SYS_CMS_DATABASE where sysid =(" + id + ")";
                }
                else
                {
                    sql = "delete FROM SYS_CMS_DATABASE where sysid ='" + id + "'";
                }
                result = Context.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;

        }
        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool UpdateDBField(SYS_CMS_DATABASE_FIELD obj)
        {
            bool result = false;
            try
            {
                result = Context.Update<SYS_CMS_DATABASE_FIELD>(obj);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }
        /// <summary>
        /// 删除对应的数据库映射字段SYS_CMS_US_FIELDMAP
        /// </summary>
        /// <param name="dbcode"></param>
        /// <returns></returns>
        public bool DelDBFieldMap(string strid)
        {
            bool result = false;
            try
            {
                strid = strid.TrimEnd(',').TrimStart(',');
                string sql = "delete FROM SYS_CMS_US_FIELDMAP where Fieldid ='" + strid + "'";
                if (strid.IndexOf(',') >= 0)
                {
                    strid = strid.Replace(',', '+');
                    sql = "delete FROM SYS_CMS_US_FIELDMAP where Fieldid =(" + strid + ")";
                }
                result = Context.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }
        /// <summary>
        /// 根据数据库编码删除对应的数据库字段
        /// </summary>
        /// <param name="dbcode"></param>
        /// <returns></returns>
        public bool DelFieldByDBCode(string dbcode)
        {
            bool result = false;
            try
            {
                string sql = "delete FROM SYS_CMS_DATABASE_FIELD where databasecode ='" + dbcode + "'";
                result = Context.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }
    }
}
